package pers.vic.util.excel.other;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.exception.ExceptionUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import pers.vic.util.ReflectionUtils;


/**
 * 描述: Excel读取处理
 */
public class ExcelImport {
	private static final Logger logger = LoggerFactory.getLogger(ExcelImport.class);
	// excel2003扩展名
	public static final String EXCEL03_EXTENSION = ".xls";
	// excel2007扩展名
	public static final String EXCEL07_EXTENSION = ".xlsx";

	private ExcelImport() {
	}

	/**
	 * 通过解析MultipartFile对象获取excel内容，并且将其拼装为List<String[]>对象返回
	 *
	 * @param excelFile
	 * @return
	 * @throws Exception
	 */
	public static List<String[]> getExcelData(File excelFile) {
		List<String[]> resultList = null;

		if (excelFile.exists()) {// 上传的文件不能为空
			String excelFileName = excelFile.getName();// 文件名（带后缀）
			if (excelFileName.toLowerCase().endsWith(EXCEL03_EXTENSION)) {// 如果文件是以.xls为后缀
				Excel2003Reader excel03 = new Excel2003Reader();// 实例化excel处理对象
				try {
					excel03.process(new FileInputStream(excelFile));
				} catch (IOException e) {
					logger.error("excel解析失败！");
					return null;
				}
				resultList = excel03.getSheetData(0);
			} else if (excelFileName.toLowerCase().endsWith(EXCEL07_EXTENSION)) {// 如果文件是以.xlsx为后缀
				Excel2007Reader excel07 = new Excel2007Reader();
				try {
					excel07.process(new FileInputStream(excelFile), false);
				} catch (Exception e) {
					logger.error("excel解析失败！");
					return null;
				}
				resultList = excel07.getSheetData(0);
			}
		}

		return resultList;
	}

	/**
	 * 根据表头和字段映射关系构造实体
	 *
	 * @param object
	 * @param headList
	 * @param headMapping
	 * @param valueMapping
	 */

	/**
	 * 根据表头和字段映射关系构造实体
	 *
	 * @param object
	 *            实体
	 * @param headList
	 *            excel抬头和实体的对应关系 编写对少关系就反射多少字段
	 * @param headMapping
	 *            excel抬头行
	 * @param valueMapping
	 *            excel数据行(必定和抬头行长度一致 且对应)
	 */
	public static void buildObj(Object object, List<ExcelTplField> headList, String[] headMapping,
			String[] valueMapping) {
		// 校验数据合法性
		if (null == object || null == headList || null == headMapping || null == valueMapping
				|| headMapping.length != valueMapping.length) {
			logger.error("excel解析失败！");
			return;
		}
		Class<? extends Object> sourceClass = object.getClass();
		Field[] fields = sourceClass.getDeclaredFields();
		for (Field field : fields) {
			out: for (ExcelTplField relation : headList) {
				if (field.getName().equals(relation.getColumn())) {// 找出关系和实体确实对应的字段
					String headName = relation.getHeadName();
					for (int i = 0; i < headMapping.length; i++) {
						if (headName.equals(headMapping[i])) {// 找出字段对应的抬头所对应的下标-->此下表和数据行的下标是一致的
							String strVal = valueMapping[i];
							Object value = strVal;
							Class<?> fieldType = field.getType();
							// 字符串
							if (fieldType.isAssignableFrom(Integer.class)) {
								value = Integer.valueOf(strVal);
							} else if (fieldType.isAssignableFrom(Long.class)) {
								value = Long.valueOf(strVal);
							} else if (fieldType.isAssignableFrom(BigDecimal.class)) {
								value = BigDecimal.valueOf(Double.valueOf(strVal));
							} else if (fieldType.isAssignableFrom(Date.class)) {
								try {
									value = DateUtils.parseDate(strVal, "yyyy-MM-dd");
								} catch (ParseException e) {
									e.printStackTrace();
								}
							} else if (fieldType.isAssignableFrom(Double.class)) {
								value = Double.valueOf(strVal);
							}
							try {
								ReflectionUtils.invokeSetter(object, field.getName(), value);

							} catch (Exception e) {
								logger.info("解析字段{}失败{}", headName, e.getMessage());
							}
							break out;
						}
					}
				}

			}
		}
	}

	public static void main(String[] args) {
		try {
			// String fileName = "F:/test.xlsx";
			String fileName = "9.9元-2018-05-30.xls";
			Excel2007Reader reader = new Excel2007Reader();
			reader.process(new FileInputStream(fileName), true);
			Map<Integer, List<String[]>> sheets = reader.getSheetData();
			System.out.println();
			List<ExcelTplField> head = Test.exportExcelTpl();
			List<String[]> datas = sheets.get(0);
			String[] headName = datas.get(0);
			for (int i = 1; i < datas.size(); i++) {
				try {
					 Test info = new Test();
					
					ExcelImport.buildObj(info, head, headName, datas.get(i));
					System.out.println(info);
				} catch (Exception e) {
					e.printStackTrace();
				} // 此处若发生异常 暂不处理
			}

		} catch (Exception e) {
			logger.error(ExceptionUtils.getStackTrace(e));
		}
	}

}

class Test {
	private String no;

	private String name;

	private Double amount;

	public static List<ExcelTplField> exportExcelTpl() {
		List<ExcelTplField> tplHeadArray = new ArrayList<ExcelTplField>();
		tplHeadArray.add(new ExcelTplField("no", "序号"));
		tplHeadArray.add(new ExcelTplField("amount", "金额"));
		tplHeadArray.add(new ExcelTplField("name", "姓名"));
		return tplHeadArray;
	}

	public String getNo() {
		return no;
	}

	public void setNo(String no) {
		this.no = no;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public Double getAmount() {
		return amount;
	}

	public void setAmount(Double amount) {
		this.amount = amount;
	}

	@Override
	public String toString() {
		return "Test [no=" + no + ", name=" + name + ", amount=" + amount + "]";
	}

}
